package com.rp.download.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * excel工具类
 *
 * @author 杨航
 */
public class ExcelUtils {

    private static final String XLSX = "xlsx";
    private static final String XLS = "xls";

    /**
     * 杨航
     * 导入Excel表格
     *
     * @param file
     * @param columns
     * @return
     */
    public static List<Map<String, String>> importExcelMap(MultipartFile file, String[] columns) throws Exception {
        List<Map<String, String>> resultList = new ArrayList<>();
        try {
            String fileName = file.getOriginalFilename();
            System.out.println(fileName);
            InputStream inputStream = file.getInputStream();
            Workbook workbook = null;
            if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            System.out.println("");
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                if(row == null){
                    continue;
                }
                Map<String, String> map = new HashMap<>();
                for (int col = 0; col < columns.length; col++) {
                    if(row.getCell(col) == null){
                        //sheet.getRow(i).getCell(col).setCellType(Cell.CELL_TYPE_STRING);
                        map.put(columns[col], "");
                    }else{
                        sheet.getRow(i).getCell(col).setCellType(CellType.STRING);
                        map.put(columns[col], getStringValue(row.getCell(col)));
                    }

                }
                resultList.add(map);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new Exception("excel解析异常");
        }
        return resultList;
    }

    /**
     * 杨航
     * 解析excel
     *
     * @param file
     * @return
     */
    public static List<List<Object>> importExcelObject(MultipartFile file) throws Exception {
        List<List<Object>> result = new ArrayList<>();
        try {
            String fileName = file.getOriginalFilename();
            InputStream inputStream = file.getInputStream();
            Workbook workbook = null;
            if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            }
            //获取第一个sheet(目前只支持一个sheet，后续可以加多个sheet)
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                List<Object> list = new ArrayList<>();
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    list.add(ExcelUtils.getStringValue(row.getCell(j)));
                }
                result.add(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("excel解析异常");
        }
        return result;
    }

    /**
     * 杨航
     * 导入Excel表格
     *
     * @param file
     * @return
     */
    public static List<JSONObject> importExcelJson(MultipartFile file) throws Exception {
        List<JSONObject> resultList = new ArrayList<>();
        try {
            String fileName = file.getOriginalFilename();
            System.out.println(fileName);
            InputStream inputStream = file.getInputStream();
            Workbook workbook = null;
            if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                JSONObject jsonObject = new JSONObject();
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    jsonObject.put("cell" + j, getStringValue(row.getCell(j)));
                }
                resultList.add(jsonObject);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new Exception("excel解析异常");
        }
        return resultList;
    }

    /**
     * Excel导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtils.getDate();
        }
        ExportParams exportParams = new ExportParams();
        exportParams.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        cellStyle.setFont(font);
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ExcelUtils.XLSX);
        response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * 杨航
     * 获取cell的值
     *
     * @param cell
     * @return String
     */
    public static String getStringValue(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy/MM/dd");
        DecimalFormat decimalFormat = new DecimalFormat("#.#");
        String cellValue = StringUtils.EMPTY;
        if (cell == null) {
            return cellValue;
        } else if (cell.getCellType() == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                cellValue = sFormat.format(date);
            } else {
                cellValue = decimalFormat.format((cell.getNumericCellValue()));
            }
        } else if (cell.getCellType() == CellType.BLANK) {
            cellValue = StringUtils.EMPTY;
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.ERROR) {
            cellValue = StringUtils.EMPTY;
        } else if (cell.getCellType() == CellType.FORMULA) {
            cellValue = cell.getCellFormula().toString();
        }
        return cellValue;
    }

//    public static String getStringValue(Cell cell) {
//        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy/MM/dd");
//        DecimalFormat decimalFormat = new DecimalFormat("#.#");
//        String cellValue = StringUtils.EMPTY;
//        if (cell == null) {
//            return cellValue;
//        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
//            cellValue = cell.getStringCellValue();
//        } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
//            if (HSSFDateUtil.isCellDateFormatted(cell)) {
//                double d = cell.getNumericCellValue();
//                Date date = HSSFDateUtil.getJavaDate(d);
//                cellValue = sFormat.format(date);
//            } else {
//                cellValue = decimalFormat.format((cell.getNumericCellValue()));
//            }
//        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
//            cellValue = StringUtils.EMPTY;
//        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
//            cellValue = String.valueOf(cell.getBooleanCellValue());
//        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
//            cellValue = StringUtils.EMPTY;
//        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
//            cellValue = cell.getCellFormula().toString();
//        }
//        return cellValue;
//    }

}
